package work;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import templateExecel2.fram.Renderer;
import templateExecel2.utils.PoiUtils;
import work.config.Constant;
import work.fram.JDBCConnect;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class 将数据库中的表结构提取为Excel模板 {
    public static void main(String[] args) throws SQLException, IOException {

        String s =
                "tc_project\n" +
                        "tc_project_contract\n" +
                        "tc_project_contract_borrow\n" +
                        "tc_project_contract_legal_authorized\n" +
                        "tc_project_ending\n" +
                        "tc_project_financial_account\n" +
                        "tc_project_market_disclose\n" +
                        "tc_project_official_seal\n" +
                        "tc_project_party_corps\n" +
                        "tc_project_personnel_order\n" +
                        "tc_project_tender_documents\n" +
                        "tc_project_winning_notice\n" +
                        "tc_md_materials\n" +
                        "tc_md_material_catalog";


        String[] split = s.split("\n");


        Map<String, List<Map<String, String>>> map = new HashMap<>();

        for (String tableName : split) {

            Statement statement = JDBCConnect.getStatement();

            ResultSet query = statement.executeQuery("SELECT\n" +
                    "    *\n" +
                    "\t\t\n" +
                    "FROM\n" +
                    "  INFORMATION_SCHEMA.COLUMNS\n" +
                    "WHERE\n" +
                    "  table_schema = 'mdm'\n" +
                    "AND\n" +
                    "  table_name = '" + tableName + "'\n");


            List<String> list = Arrays.asList("COLUMN_NAME", "DATA_TYPE", "COLUMN_TYPE", "CHARACTER_MAXIMUM_LENGTH", "IS_NULLABLE", "COLUMN_DEFAULT", "COLUMN_COMMENT");

            List<Map<String, String>> li = new ArrayList<>();

            while (query.next()) {
                Map<String, String> r = new HashMap<>();
                list.forEach(name -> {
                    try {
                        String value = query.getString(name);
                        if (value == null) {
                            r.put(name, "");
                        } else {
                            r.put(name, value);
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                });
                li.add(r);
            }

            map.put(tableName, li);

        }


        File template = new File(new File(Constant.work, "模板"), "文档.xlsx");

        XSSFWorkbook workbook = PoiUtils.read(template);

        //模板
        XSSFSheet templateSheet = workbook.getSheet("表结构模板");

        map.forEach((tn, list) -> {

            XSSFSheet targetSheet = workbook.createSheet(tn);

            Map<String, Object> data = new HashMap<>();

            data.put("list", list);
            data.put("bm", tn);

            Renderer.render(data, templateSheet, targetSheet);

        });

        File target = new File(Constant.work, "目标文件.xlsx");


        PoiUtils.write(workbook,target);

    }


}
